from django.db import models

# Create your models here.
from xuexin_db.models import TStuconfirmMaster  # 学生成绩确认表主表
from xuexin_db.models import TStuscoreconfDetail  # 学生成绩确认表从表
from django.db import connection


class StudentsModel:
    """
    学生信息查询方法工具类
    """

    def query_student(self, student_no="", student_name="", student_org_name="", student_major="", first=0, limit=20):
        """
        条件查询学生信息,模糊匹配
        studentNo 学号
        studentName 学生名字 
        student_org_name 学院
        student_major 专业
        first 数据开始位置,默认为0
        limit 查询结果长度,默认20
        return list(结果集)
        """

        # 学生名字和学号模糊查询
        students = TStuconfirmMaster.objects \
                       .filter(studentno__startswith=student_no) \
                       .filter(stuname__icontains=student_name)[first:limit]
        # 在上面的基础上,添加专业查询
        if len(student_major) > 0:
            students = students.filter(major=student_major)
        # 在上面的基础上,添加学院的查询
        if len(student_org_name) > 0:
            students = students.filter(org_name=student_org_name)

        # 学生信息列表
        student_list = []
        for stu in students:
            student_list.append({
                "student_no": stu.studentno,  # 学号
                "student_name": stu.stuname,  # 姓名
                "org_name": stu.org_name,  # 学院
                "major": stu.major  # 专业
            })
        return student_list

    def students_grade(self, student_no, term="", academic_year="", is_pass=""):
        """
        学生成绩查询
        :param student_no:学号,必须
        :param term:学期
        :param academic_year:学年
        :param is_pass:及格标记,Bool 值,True 返回及格成绩,False返回不及格成绩,缺省返回所有成绩
        :return: list(成绩信息列表)
        """
        # FINALSCORE	总评成绩
        # employ_no	任课老师工号
        # employ_name	任课老师名字
        # studentno		学号
        # stuname		姓名
        # team		成绩学期
        sql = "SELECT master_.TERM, " \
              "master_.STUDENTNO, " \
              "master_.STUNAME, " \
              "detail.FINALSCORE, " \
              "detail.EMPLOY_NO, " \
              "detail.EMPLOY_NAME, " \
              "detail.COURSECODE, " \
              "detail.COURSENAME, " \
              "master_.ACADEMICYEAR " \
              "FROM t_stuscoreconf_detail AS detail " \
              "INNER JOIN t_stuscoreconf_master AS master_ ON detail.MASTER_ID = master_.ID " \
              "WHERE master_.STUDENTNO = %s " \
              "AND master_.TERM LIKE %s " \
              "AND master_.ACADEMICYEAR LIKE %s"

        # 如果有is_pass标记,
        # 且为真,添加及格过滤条件
        # 若为假,添加不及格过滤条件
        if len(is_pass) > 0:
            is_pass = is_pass.upper()
            if is_pass == "TRUE":
                sql = sql + " AND (" \
                            "(" \
                            "FINALSCORE>=60 " \
                            "OR FINALSCORE='及格' " \
                            "OR FINALSCORE='中等' " \
                            "OR FINALSCORE='良好' " \
                            "OR FINALSCORE='优秀'" \
                            ") " \
                            "AND FINALSCORE != '不及格') "
            elif is_pass == "FALSE":
                sql = sql + " AND (" \
                            "(" \
                            "FINALSCORE < 60 " \
                            "OR FINALSCORE = '不及格'" \
                            ") " \
                            "AND " \
                            "FINALSCORE != '及格' " \
                            "AND FINALSCORE != '中等' " \
                            "AND FINALSCORE != '良好' " \
                            "AND FINALSCORE != '优秀' ) "
        with connection.cursor() as cursor:
            term = term if len(term) > 0 else "_"
            academic_year = academic_year if len(academic_year) > 0 else "%"
            # 参数注入并执行sql语句
            cursor.execute(sql, [student_no, term, academic_year])
            # 获取数据表头
            columns = [col[0].lower() for col in cursor.description]
            # 将数据和表头打包成字典列表然后return
            return [
                dict(zip(columns, row)) for row in cursor.fetchall()
            ]
